import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;
import org.springframework.web.client.RestTemplate;

import java.io.*;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.*;

@RunWith(SpringJUnit4ClassRunner.class) //使用junit4进行测试
@ContextConfiguration(locations={"classpath:spring.xml"}) //加载配置文件
public class testLBS {
  @Autowired
    RestTemplate restTemplate;
    public String rootPath="F:/LBS的数据/";
  @Test
   public void getData(){
       Map jsonObjPms=new HashMap<String,Object>();
       int page_index=0;

      int size=50;
      JSONObject jsonObject=new JSONObject();
//      JSONArray contents= new JSONArray();
      List list = new ArrayList();
       while (size !=0) {
           String url= "http://api.map.baidu.com/geosearch/v3/local?ak=xR1l7xXKtptr0k1eyvxxFZOa&geotable_id=131456&page_size=50&page_index="+page_index;
           System.out.println(page_index+"条记录");
           Map forObject = restTemplate.getForObject(url, Map.class);
//           jsonObject=JSONObject.parseObject(forObject.toString());
           ArrayList temcontents = (ArrayList) forObject.get("contents");
           list.addAll(temcontents);
           size =Integer.valueOf(forObject.get("size").toString()) ;
           page_index++;
//           break;
       }
//      DataColum[] values = DataColum.values();
//      String s = values.toString();
      System.out.println(list);
//       createData(list);
      importDataToExcel(list);
   }

   public void createData(List data){

       for (int i = 0; i < 2; i++) {
//           String params = data.get(i);
           String s = JSONObject.toJSONString(data.get(i));
           JSONObject paramsJson = JSONObject.parseObject(s);
           JSONArray locationArray = (JSONArray)paramsJson.get("location");
           double longitude = Double.valueOf(locationArray.get(0).toString());
           double latitude = Double.valueOf(locationArray.get(1).toString());
           paramsJson.remove("location");
           MultiValueMap<String, Object> multiValueMap= new LinkedMultiValueMap<String, Object>();
           Set<Map.Entry<String, Object>> entries = paramsJson.entrySet();
           Iterator<Map.Entry<String, Object>> iterator = entries.iterator();
           while (iterator.hasNext()){
               Map.Entry<String, Object> next = iterator.next();
               multiValueMap.add(next.getKey(),String.valueOf(next.getValue()));
           }
           String url = "http://api.map.baidu.com/geodata/v3/poi/create";
           MediaType type = MediaType.parseMediaType("application/json; charset=UTF-8");
           HttpHeaders headers = new HttpHeaders();
           headers.setContentType(MediaType.MULTIPART_FORM_DATA);
//       headers.add("Accept", MediaType.APPLICATION_FORM_URLENCODED.toString());

           multiValueMap.add("ak", "bIC21V92LTkSDAEWUez0Sl3GOF6aal1Q");
           multiValueMap.add("geotable_id", "174784");
           multiValueMap.add("latitude", String.valueOf(latitude));
           multiValueMap.add("longitude", String.valueOf(longitude));

           HttpEntity<MultiValueMap<String, Object>> request = new HttpEntity<MultiValueMap<String, Object>>(multiValueMap, headers);
           String o = restTemplate.postForObject(url, request, String.class);
           System.out.println(o);
       }
   }

   @Test
   public void deldata(){
       String url ="http://api.map.baidu.com/geodata/v3/poi/delete";
       HttpHeaders headers = new HttpHeaders();
       headers.setContentType(MediaType.MULTIPART_FORM_DATA);
//       headers.add("Accept", MediaType.APPLICATION_FORM_URLENCODED.toString());
       MultiValueMap<String, Object> multiValueMap= new LinkedMultiValueMap<String, Object>();
       multiValueMap.add("ak", "bIC21V92LTkSDAEWUez0Sl3GOF6aal1Q");
       multiValueMap.add("geotable_id", "174784");
       multiValueMap.add("is_total_del", "1");
       HttpEntity<MultiValueMap<String, Object>> request = new HttpEntity<MultiValueMap<String, Object>>(multiValueMap, headers);
       String o = restTemplate.postForObject(url, request, String.class);
       System.out.println(o);
   }

    /**
     * 得到百度LBS的字段列表
     */
    @Test
   public void getColum(){
        String ak="xR1l7xXKtptr0k1eyvxxFZOa";//"bIC21V92LTkSDAEWUez0Sl3GOF6aal1Q";
        String geotable_id="131456";//"174784";
       String url ="http://api.map.baidu.com/geodata/v3/column/list?ak="+ak+"&geotable_id="+geotable_id;
       String forObject = restTemplate.getForObject(url, String.class);
       writeFile(forObject,rootPath+ak+"_"+getNowDate()+".txt");
       System.out.println(forObject);
   }


   public void importDataToExcel(List data){
       FileInputStream in = null;
       FileOutputStream out=null;
       try {
            in = new FileInputStream("F:/databox_174784.xlsx");
           XSSFWorkbook sheets = new XSSFWorkbook(in);
           XSSFSheet sheet = sheets.getSheetAt(0);
           sheet.autoSizeColumn(0,true);

           XSSFRow row=null;
           XSSFCell cell =null;
           XSSFCellStyle cellStyle = sheets.createCellStyle();
           XSSFDataFormat format = sheets.createDataFormat();
           //这样才能真正的控制单元格格式，@就是指文本型，具体格式的定义还是参考上面的原文吧
           cellStyle.setDataFormat(format.getFormat("@"));

           for (int i = 0; i < data.size(); i++) {
               System.out.println("第几"+(i+1)+"行开始");
               Map<String,Object> o = (Map) data.get(i);
               List location = (List)o.get("location");
               double longitude = Double.valueOf(location.get(0).toString());
               double latitude = Double.valueOf(location.get(1).toString());
               o.put("longitude",longitude);
               o.put("latitude",latitude);
               DataColum[] values = DataColum.values();
               row = sheet.createRow(i+1);
               for (int j = 0; j < values.length; j++) {
                   Object o1 = o.get(values[j].getString());
                   cell = row.createCell(j);
                   //具体如何创建cell就省略了，最后设置单元格的格式这样写
                   cell.setCellStyle(cellStyle);
                   if(o1!=null){
                       cell.setCellValue(String.valueOf(o1));
                   }

               }
               System.out.println("第几"+(i+1)+"行结束");
           }

           out= new FileOutputStream("F:/databox_174784.xlsx");
           sheets.write(out);
       } catch (Exception e) {
               e.printStackTrace();
       }finally {
           try {
               in.close();
               out.close();
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
   }

    //获取单元格的值
    private String getCellValue(Cell cell) {
        String cellValue = "";
        DataFormatter formatter = new DataFormatter();
        if (cell != null) {
            //判断单元格数据的类型，不同类型调用不同的方法
            switch (cell.getCellType()) {
                //数值类型
                case Cell.CELL_TYPE_NUMERIC:
                    //进一步判断 ，单元格格式是日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = formatter.formatCellValue(cell);
                    } else {
                        //数值
                        double value = cell.getNumericCellValue();
                        int intValue = (int) value;
                        cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                //判断单元格是公式格式，需要做一种特殊处理来得到相应的值
                case Cell.CELL_TYPE_FORMULA:{
                    try{
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }catch(IllegalStateException e){
                        cellValue = String.valueOf(cell.getRichStringCellValue());
                    }

                }
                break;
                case Cell.CELL_TYPE_BLANK:
                    cellValue = "";
                    break;
                case Cell.CELL_TYPE_ERROR:
                    cellValue = "";
                    break;
                default:
                    cellValue = cell.toString().trim();
                    break;
            }
        }
        return cellValue.trim();
    }

    public enum DataColum{
        //city	district	xhid	province	uid	barcodeid	time	sshtid	companyid	picture	lxid	htbh
        title("title"),address("address"),longitude("longitude"),latitude("latitude"),coord_type("coord_type"),tags("tags")
        ,bzpbm("bzpbm"),city("city"),district("district"),	xhid("xhid"),	province("province"),	uid("uid"),
        barcodeid("barcodeid"),	time("time"),sshtid	("sshtid"),companyid("companyid"),	picture("picture"),
        lxid("lxid"),	htbh("htbh"),person("person");
        public String colum;
        private DataColum(String colum) {
            this.colum=colum;
        }

        public String getString(){
            return colum;
        }

        @Override
        public String toString() {
            return colum;
        }
    }

    public  void writeFile(String data ,String path){

        File file =new File(path);

        //if file doesnt exists, then create it
        BufferedWriter bufferWritter=null;
        try {
            if(!file.exists()){
                file.createNewFile();
            }
            //true = append file
            FileWriter fileWritter = new FileWriter(file.getAbsoluteFile(),true);
            bufferWritter = new BufferedWriter(fileWritter);
            bufferWritter.flush();
            bufferWritter.write(data);

        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                bufferWritter.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    public static String getNowDate() {
        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH_mm_ss");
        String dateString = formatter.format(currentTime);
        return dateString;
    }

}
